--    Author    : ChenErHao
--    Name      : DM.T_MTH_LOAN_BALANCE_GROWTH_TOTAL.HQL
--    Functions : 增速-贷款余额增速分析
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-24  ChenErHao       1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE DM.T_MTH_LOAN_BALANCE_GROWTH_TOTAL  PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 
SELECT
    T1.ORG_ID,
    T1.CUSTOMER_TYPE,
    T1.PRODUCT_CATEGORY,
    T1.LOAN_PERIOD, --贷款期限,
    T1.LPIC, --企业分行
    T1.LOANS_ACTUALLY, --贷款实际投向,
    T1.ENTERPRISE_SCALE,--企业规模,
    T1.ENTERPRISE_INVESTOR,--企业出资人经济成冿
    T1.GUARANTY_STYLE,--担保方式,
    T1.LOAN_STATUS,--贷款状徿
    T1.LOAN_QUALITY,--贷款质量,
    T1.AREA_NO_ID,
    nvl(sum(case when DATA_DATE='#V_DATA_DATE#' then T1.LOAN_BALANCE end),0) LOAN_BALANCE,
    nvl(sum(case when DATA_DATE='#V_LAST_1M_END_DATE#' then T1.LOAN_BALANCE end),0) LAST_MTH_LOAN_BALANCE,
    nvl(sum(case when DATA_DATE='#V_LAST_12M_END_DATE#' then T1.LOAN_BALANCE end),0) LAST_YEAR_LOAN_BALANCE,
    nvl(sum(case when DATA_DATE='#V_DATA_DATE#' then T1.LOAN_BALANCE end),0) YEAR_END_LOAN_BALANCE,
    nvl(sum(case when DATA_DATE='#V_LAST_1Y_END_DAY#' then T1.LOAN_BALANCE end),0) LAST_YEAR_END_LOAN_BALANCE,
    nvl(sum(case when DATA_DATE='#V_DATA_DATE#' then T1.WEIGHT_AMT end),0) WEIGHT_AMT
FROM
    (
        SELECT
            A.DATA_DATE,
            BELONG_AREA_CODE     AREA_NO_ID,            
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END ORG_ID, 
            A.CUSTOMER_TYPE         CUSTOMER_TYPE,--客户类型
            CASE WHEN C.loan_pro_id_1 IS NULL THEN '9999'
                 WHEN C.loan_pro_id_1 ='' THEN '9999'
                 ELSE C.loan_pro_id_1 END PRODUCT_CATEGORY,--贷款产品类别
            A.LOANS_PERIOD           LOAN_PERIOD,--贷款期罿
            LPIC_SUB LPIC,--企业分行
            CASE WHEN G.loan_actually_id_1 IS NULL THEN '9999'
                 WHEN G.loan_actually_id_1 ='' THEN '9999'
                 ELSE G.loan_actually_id_1 END LOANS_ACTUALLY,--行业投向
            nvl(ENTERPRISE_SCALE,'CS05') ENTERPRISE_SCALE,--企业规模
            CASE WHEN E.investor_finicl_id_2 IS NULL THEN '9999'
                 WHEN E.investor_finicl_id_2 ='' THEN '9999'
                 ELSE E.investor_finicl_id_2 END  ENTERPRISE_INVESTOR,--企业出资人经济成分二级编瞿
            CASE WHEN F.loan_insur_id_1 IS NULL THEN '9999'
                 WHEN F.loan_insur_id_1 ='' THEN '9999'
                 ELSE F.loan_insur_id_1 END   GUARANTY_STYLE,--贷款担保方式一级编码
            nvl(A.LOAN_STATUS,'9999')            LOAN_STATUS,--贷款状徿
            nvl(A.LOAN_QUALITY,'9999')         LOAN_QUALITY,--贷款质量,
            SUM(LOAN_BALANCE)        LOAN_BALANCE,
            SUM(LOAN_OCCURRENCE_LEVEL)  WEIGHT_AMT
        FROM
           (SELECT DATA_DATE,CUSTOMER_TYPE,LOANS_PERIOD,BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB,LOANS_ACTUALLY,ENTERPRISE_INVESTOR,GUARANTY_STYLE,
           ENTERPRISE_SCALE,LOAN_STATUS,LOAN_QUALITY,LOAN_BALANCE,LOAN_OCCURRENCE_LEVEL
            FROM EDW.LOAB
        WHERE
            DATA_DATE in ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#','#V_LAST_1Y_END_DAY#')  )A
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.DATA_ORG_CODE end = B.ORG_ID  --金融机构
        LEFT JOIN DMCODE.T_LOAN_PRO_TYPE C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.PRODUCT_CATEGORY end= C.LOAN_PRO_ID --贷款产品类别
        LEFT JOIN DMCODE.T_LOANS_ACTUALLY G ON CASE WHEN a.LOANS_ACTUALLY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.LOANS_ACTUALLY end=G.loan_actually_id --贷款行业投向
        LEFT JOIN DMCODE.T_INVESTOR_FINICL_TYPE_TRANS E ON CASE WHEN a.ENTERPRISE_INVESTOR IS NULL THEN CONCAT('org_code_hive',RAND()) else A.ENTERPRISE_INVESTOR end= E.INVESTOR_FINICL_ID --企业出资人经济成冿
        LEFT JOIN DMCODE.T_LOAN_INSUR_TYPE F ON CASE WHEN a.GUARANTY_STYLE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.GUARANTY_STYLE end=F.LOAN_INSUR_ID --贷款担保方式
        GROUP BY
            A.DATA_DATE ,
            A.BELONG_AREA_CODE ,            
            B.ORG_LVL_ID_2 ,          
            A.CUSTOMER_TYPE,            
            C.LOAN_PRO_ID_1,             
            A.LOANS_PERIOD,--贷款期限           
            LPIC_SUB ,                       
            G.loan_actually_id_1  ,
            A.ENTERPRISE_SCALE,--企业规模,
            E.INVESTOR_FINICL_ID_2 ,
            F.LOAN_INSUR_ID_1,             
            A.LOAN_STATUS,
            A.LOAN_QUALITY) T1
GROUP BY
    T1.ORG_ID,
    T1.CUSTOMER_TYPE,
    T1.PRODUCT_CATEGORY,
    T1.LOAN_PERIOD, 
    T1.LPIC, 
    T1.LOANS_ACTUALLY, 
    T1.ENTERPRISE_SCALE,
    T1.ENTERPRISE_INVESTOR,
    T1.GUARANTY_STYLE,
    T1.LOAN_STATUS,
    T1.LOAN_QUALITY,
    T1.AREA_NO_ID 
;